package com.attendancesystem.service;

import com.attendancesystem.model.Grade;
import com.attendancesystem.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GradesService {
    public List<Grade> getGrades() throws SQLException {
        String sql = "SELECT g.*, s.name AS student_name " +
                "FROM grades g " +
                "INNER JOIN students s ON g.student_id = s.id";

        List<Grade> grades = new ArrayList<>();
        try (Connection con = JDBCUtils.getConnection();
             PreparedStatement pstmt = con.prepareStatement(sql)) {
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Grade grade = new Grade();
                    grade.setGradeId(rs.getInt("grade_id"));
                    grade.setStudentId(rs.getString("student_id"));
                    grade.setCourseName(rs.getString("course_name"));
                    grade.setUsualScore(rs.getBigDecimal("usual_score"));
                    grade.setFinalScore(rs.getBigDecimal("final_score"));
                    grade.setTotalScore(rs.getBigDecimal("total_score"));
                    grade.setStudentName(rs.getString("student_name"));
                    grades.add(grade);
                }
            }

        }catch (SQLException e){
            e.printStackTrace();
        }
        return grades;

    }

}
